"""
Case Type   : 事件触发器
Case Name   : 【事件触发器】一个事件指定了多个事件触发器，根据事件触发器的名称字母序升序调用
Create At   :   2024/07/20
Owner       :   sungang14
Description :
    预置条件：
        1.创建事件触发器函数：
        CREATE OR REPLACE FUNCTION f_eventtrigger_0064_1()
        RETURNS event_trigger
        LANGUAGE plpgsql
        AS $$ BEGIN
        RAISE NOTICE 'test_event_trigger1: % %', tg_event, tg_tag;
        END;
        $$;
        CREATE OR REPLACE FUNCTION f_eventtrigger_0064_2()
        RETURNS event_trigger
        LANGUAGE plpgsql
        AS $$ BEGIN
        RAISE NOTICE 'test_event_trigger2: % %', tg_event, tg_tag;
        END;
        $$;
        CREATE OR REPLACE FUNCTION f_eventtrigger_0064_3()
        RETURNS event_trigger
        LANGUAGE plpgsql
        AS $$ BEGIN
        RAISE NOTICE 'test_event_trigger3: % %', tg_event, tg_tag;
        END;
        $$;
    测试步骤：
        1.创建事件触发器
        CREATE EVENT TRIGGER eventtri_eventtrigger_0064_3 ON ddl_command_start WHEN TAG IN ('CREATE TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_1();
        CREATE EVENT TRIGGER eventtri_eventtrigger_0064_2 ON ddl_command_start WHEN TAG IN ('CREATE TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_2();
        CREATE EVENT TRIGGER eventtri_eventtrigger_0064_1 ON ddl_command_start WHEN TAG IN ('CREATE TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_3();
        2.创建表
        create table t_eventtrigger_0064(id int);
        3、创建事件触发器
        CREATE EVENT TRIGGER bgggeventtri_eventtrigger_0064 ON ddl_command_start WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_1();
        CREATE EVENT TRIGGER assseventtri_eventtrigger_0064 ON ddl_command_start WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_2();
        CREATE EVENT TRIGGER ckkkeventtri_eventtrigger_0064 ON ddl_command_start WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_3();
        4.创建表
        drop table if exists t_eventtrigger_0064;
Expect      : 
        1.创建事件触发器成功
        2.触发执行函数顺序
        test_event_trigger3: ddl_command_start,CREATE TABLE
        test_event_trigger2: ddl_command_start,CREATE TABLE
        test_event_trigger1: ddl_command_start,CREATE TABLE
        3、创建事件触发器成功
        4、 test_event_trigger2: ddl_command_start,DROP TABLE
        test_event_trigger1: ddl_command_start,DROP TABLE
        test_event_trigger3: ddl_command_start,DROP TABLE
History     : 
"""

import sys
import os
import unittest
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH


class IndexFileDamaged(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.logger.info('----------------this is setup-----------------------')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH
        self.comsh = CommonSH('PrimaryDbBmode')
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0064_1;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0064_2;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0064_3;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists bgggeventtri_eventtrigger_0064;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists assseventtri_eventtrigger_0064;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists ckkkeventtri_eventtrigger_0064;")
        self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0064_1();")
        self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0064_2();")
        self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0064_3();")
        self.comsh.execut_db_sql("drop TABLE if exists t_eventtrigger_0064;")
        msg = self.comsh.execut_db_sql(
            f"CREATE OR REPLACE FUNCTION f_eventtrigger_0064_1() RETURNS event_trigger LANGUAGE plpgsql" \
            f" AS \$\$ BEGIN" \
            f" RAISE NOTICE 'test_event_trigger1: % %', tg_event, tg_tag;" \
            f"END;" \
            f"\$\$;")
        self.logger.info(msg)
        self.assertIn("CREATE FUNCTION", msg)
        msg = self.comsh.execut_db_sql(
            f"CREATE OR REPLACE FUNCTION f_eventtrigger_0064_2() RETURNS event_trigger LANGUAGE plpgsql" \
            f" AS \$\$ BEGIN" \
            f" RAISE NOTICE 'test_event_trigger2: % %', tg_event, tg_tag;" \
            f"END;" \
            f"\$\$;")
        self.logger.info(msg)
        self.assertIn("CREATE FUNCTION", msg)
        msg = self.comsh.execut_db_sql(
            f"CREATE OR REPLACE FUNCTION f_eventtrigger_0064_3() RETURNS event_trigger LANGUAGE plpgsql" \
            f" AS \$\$ BEGIN" \
            f" RAISE NOTICE 'test_event_trigger3: % %', tg_event, tg_tag;" \
            f"END;" \
            f"\$\$;")
        self.logger.info(msg)
        self.assertIn("CREATE FUNCTION", msg)

    def test_Index_file_damaged(self):
        self.logger.info("--step1:创建3个事件触发器")
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER eventtri_eventtrigger_0064_3 ON ddl_command_start WHEN TAG IN ('CREATE TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_1();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER eventtri_eventtrigger_0064_2 ON ddl_command_start WHEN TAG IN ('CREATE TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_2();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER eventtri_eventtrigger_0064_1 ON ddl_command_start WHEN TAG IN ('CREATE TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_3();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        self.logger.info("--step2:创建表")
        msg = self.comsh.execut_db_sql("create table t_eventtrigger_0064(id int);")
        self.logger.info(msg)
        self.assertIn(
            f'NOTICE:  test_event_trigger3: ddl_command_start CREATE TABLE\nNOTICE:  test_event_trigger2: ddl_command_start CREATE TABLE\nNOTICE:  test_event_trigger1: ddl_command_start CREATE TABLE' \
            or f'NOTICE:  test_event_trigger3: ddl_command_start CREATE TABLE\nCREATE TABLE\nNOTICE:  test_event_trigger2: ddl_command_start CREATE TABLE\nNOTICE:  test_event_trigger1: ddl_command_start CREATE TABLE' \
            or f'NOTICE:  test_event_trigger3: ddl_command_start CREATE TABLE\nNOTICE:  test_event_trigger2: ddl_command_start CREATE TABLE\nCREATE TABLE\nNOTICE:  test_event_trigger1: ddl_command_start CREATE TABLE',
            msg)
        self.logger.info("--step3:创建3个事件触发器")
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER bgggeventtri_eventtrigger_0064 ON ddl_command_start WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_1();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER assseventtri_eventtrigger_0064 ON ddl_command_start WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_2();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER ckkkeventtri_eventtrigger_0064 ON ddl_command_start WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE f_eventtrigger_0064_3();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        self.logger.info("--step4:删除表")
        msg = self.comsh.execut_db_sql("drop table if exists t_eventtrigger_0064;")
        self.logger.info(msg)
        self.assertIn(
            f'NOTICE:  test_event_trigger2: ddl_command_start DROP TABLE\nNOTICE:  test_event_trigger1: ddl_command_start DROP TABLE\nNOTICE:  test_event_trigger3: ddl_command_start DROP TABLE' \
            or f'NOTICE:  test_event_trigger2: ddl_command_start DROP TABLE\nCREATE TABLE\nNOTICE:  test_event_trigger1: ddl_command_start DROP TABLE\nNOTICE:  test_event_trigger3: ddl_command_start DROP TABLE' \
            or f'NOTICE:  test_event_trigger2: ddl_command_start DROP TABLE\nNOTICE:  test_event_trigger1: ddl_command_start DROP TABLE\nCREATE TABLE\nNOTICE:  test_event_trigger3: ddl_command_start DROP TABLE',
            msg)

    def tearDown(self):
        self.logger.info('----------------this is tearDown-----------------------')
        # 删除事件触发器
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0064_1;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0064_2;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0064_3;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists bgggeventtri_eventtrigger_0064;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists assseventtri_eventtrigger_0064;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists ckkkeventtri_eventtrigger_0064;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        # 删除事件触发器函数
        msg = self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0064_1();")
        self.logger.info(msg)
        self.assertIn('DROP FUNCTION', msg)
        msg = self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0064_2();")
        self.logger.info(msg)
        self.assertIn('DROP FUNCTION', msg)
        msg = self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0064_3();")
        self.logger.info(msg)
        self.assertIn('DROP FUNCTION', msg)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
